﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Oracle.DataAccess.Client;
using System.Security.Principal;

namespace OraWinApp
{
    public partial class TabForm : Form
    {
        #region Private form variables
        private string _username;
        private string _password;
        private bool _loggedIn = false;

        private string oradb = "Data Source=localhost;Persist Security Info=True;User ID=hr; Password=hr";
        private OracleConnection _conn = new OracleConnection();

        #endregion        
        
        public TabForm()
        {
            InitializeComponent();
        }

        #region Button Click Event Handlers
        private void button1_Click(object sender, EventArgs e)
        {
            string oradb = "Data Source=localhost;Persist Security Info=True;User ID=hr; Password=hr";
            string oradbClientID = "Data Source=localhost;Persist Security Info=True;User ID=hr; Password=hr";

            //The proxy user feature of Oracle will work only on a Oracle Enterprise edition DB
            //the version we have available of oracle is the express version, and in order to use the fine grained security features of oracle, 
            //the Enterprise edition is needed.

            //string oradb = "DATA SOURCE=;PERSIST SECURITY INFO=False;User Id=ActualUser;Password=secret; PROXY USER ID=HR; Proxy Password= hr";  

            using (OracleConnection connection = new OracleConnection())
            {
                WindowsPrincipal user = new WindowsPrincipal(WindowsIdentity.GetCurrent());

                connection.ConnectionString = oradb;
                connection.Open();

                //fetching the windows Identity of the user logged on to the machine
                lblWindowsUserID.Visible = true;
                connection.ClientId = user.Identity.Name;
                label2.Text = user.Identity.Name;

                Console.WriteLine("State: {0}", connection.State);
                Console.WriteLine("ConnectionString: {0}",
                                  connection.ConnectionString);

                OracleCommand command = connection.CreateCommand();
                //This is the SQL command wihtout the use of parameters, and susceptible to SQLIA                
                command.CommandText = "Select Count(*) from Employees Where JOB_ID = '" + textBox1.Text + "'";


                OracleCommand commandUpdated = connection.CreateCommand();
                commandUpdated.CommandText = "select count(*) from Employees where JOB_ID = :JOB_ID";

                OracleParameter p1 = new OracleParameter("JOB_ID", OracleDbType.Varchar2);
                p1.Value = textBox1.Text;
                commandUpdated.Parameters.Add(p1);


                lblNonParameterized.Visible = true;
                lblNonParameterizedResult.Visible = true;
                lblParameterized.Visible = true;
                lblParameterizedResult.Visible = true;

                object count2 = commandUpdated.ExecuteScalar();
                lblParameterizedResult.Text = count2.ToString();

                object count = command.ExecuteScalar();
                lblNonParameterizedResult.Text = count.ToString();
            }

            #region Using Client ID along with connection pooling
            using (OracleConnection connection = new OracleConnection())
            {
                lblClientID.Visible = true;
                lblClientIDValue.Visible = true;

                connection.ConnectionString = oradbClientID;

                connection.Open();
                //setting the connection client ID which does not spawn a separate session for connection pooling
                connection.ClientId = "SomeUser";

                OracleCommand command = connection.CreateCommand();
                command.CommandText = "SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') FROM DUAL";

                OracleDataReader dr = command.ExecuteReader();
                dr.Read();
                lblClientIDValue.Text = dr.GetString(0);
            }
            #endregion

        }

        protected void ButtonLogin_Click(object sender, EventArgs e)
        {
            _username = txtUserName.Text;
            _password = txtPassword.Text;

            if (!IsNonEmptyCredentials())
            {
                lblResult.Text = "ERROR: Empty credentils not permitted";
                return;
            }

            if (AttemptSQLLogin())
            {
                FillOrderData("unparameterized");
                EnableLoggedInVisuals();
            }

            else
            {
                DisableLoggedInVisuals();
            }

        }

        private void ButtonLogout_Click(object sender, EventArgs e)
        {
            lblResult.Text = "Logged Out";
            _loggedIn = false;
            _username = "";
            _password = "";
            txtUserName.Text = "";
            txtPassword.Text = "";

            DisableLoggedInVisuals();
        }

        private void btnLoginParameterized_Click(object sender, EventArgs e)
        {
            _username = txtUserNameParameterized.Text;
            _password = txtPasswordParameterized.Text;

            if (!IsNonEmptyCredentials())
            {
                lblResultParameterized.Visible = true;
                lblResultParameterized.Text = "ERROR: Empty credentils not permitted";
                return;
            }

            if (AttemptParameterizedSQLLogin())
            {
                lblResultParameterized.Visible = true;
                FillOrderData("parameterized");
                EnableLoggedInVisuals();
            }

            else
            {
                DisableLoggedInVisuals();
            }

        }

        private void ButtonLogoutParameterized_Click(object sender, EventArgs e)
        {
            lblResultParameterized.Text = "Logged Out";
            _loggedIn = false;
            _username = "";
            _password = "";
            txtUserNameParameterized.Text = "";
            txtPasswordParameterized.Text = "";

            DisableLoggedInVisuals();
        }

        #endregion

        #region Helper Methods

        protected bool IsNonEmptyCredentials()
        {
            if (_username == null || _username.Length == 0 || _password == null || _password.Length == 0)

                return false;

            else
                return true;
        }

        protected bool AttemptParameterizedSQLLogin()
        {
            try
            {
                _conn = new OracleConnection(oradb);
                _conn.Open();

            }
            catch (Exception ex)
            {
                lblResultParameterized.Text = String.Format("ERROR: Failed to open SQL Connection: {0}", ex.Message);
                return false;
            }

            OracleDataReader dataReader = null;
        
            string command = "select * from Users where Username = : Username AND Pssword = : Password";

            OracleCommand commandUpdated = new OracleCommand(command,_conn);

            OracleParameter p1 = new OracleParameter("Username", OracleDbType.Varchar2);
            p1.Value = _username;
            commandUpdated.Parameters.Add(p1);

            OracleParameter p2 = new OracleParameter("Password", OracleDbType.Varchar2);
            p2.Value = _password;
            commandUpdated.Parameters.Add(p2);

            try
            {
                dataReader = commandUpdated.ExecuteReader(CommandBehavior.SingleResult);

                if (dataReader.HasRows)
                {
                    lblResultParameterized.Text = String.Format("Login Success");
                    dataReader.Close();
                    _loggedIn = true;
                    return true;
                }

                else
                {
                    lblResultParameterized.Visible = true;
                    lblResultParameterized.Text = String.Format("Login failed: Invalid credentials");
                    dataReader.Close();
                    return false;
                }
            }
            catch (Exception ex)
            {
                lblResultParameterized.Text = String.Format("ERROR: Failed to execute SQL command: {0}", ex.Message);
                return false;
            }
        }

        protected bool AttemptSQLLogin()
        {
            try
            {
                _conn = new OracleConnection(oradb);
                _conn.Open();

            }
            catch (Exception ex)
            {
                lblResult.Text = String.Format("ERROR: Failed to open SQL Connection: {0}", ex.Message);
                return false;
            }

            OracleDataReader dataReader = null;

            string SQLQuery = String.Format(
            "SELECT * FROM Users WHERE Username='{0}' AND Pssword='{1}'",
            _username, _password);

            OracleCommand command = new OracleCommand(SQLQuery, _conn);

            try
            {
                dataReader = command.ExecuteReader(CommandBehavior.SingleResult);

                if (dataReader.HasRows)
                {
                    lblResult.Text = String.Format("Login Success");
                    dataReader.Close();
                    _loggedIn = true;
                    return true;
                }

                else
                {
                    lblResult.Text = String.Format("Login failed: Invalid credentials");
                    dataReader.Close();
                    return false;
                }
            }
            catch (Exception ex)
            {
                lblResult.Text = String.Format("ERROR: Failed to execute SQL command: {0}", ex.Message);
                return false;
            }
        }

        protected void FillOrderData(string Method)
        {
            string method = Method;
            //switch (method)
            //{
            //    case "unparameterized":
            if(method =="unparameterized")
                    {
                        if (!_loggedIn)
                        {
                            lblResult.Text = "No user logged in";
                            //return false;
                        }
                        else
                        {
                            try
                            {
                                string SQLQuery = String.Format(
                                "SELECT Distinct Orders.OrderId, Orders.Amount, Orders.CreditCard " +
                                 "FROM Users, Orders WHERE Users.Username='{0}' " +
                                 "AND Users.UserId=Orders.UserId", _username);
                                OracleCommand command = new OracleCommand(SQLQuery, _conn);

                                OracleDataAdapter dataAdapter = new OracleDataAdapter(command);
                                OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter);

                                // Populate a new data table and bind it to the BindingSource.
                                DataTable table = new DataTable();
                                table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                                dataAdapter.Fill(table);
                                BindingSource dbBindSource = new BindingSource();

                                dbBindSource.DataSource = table;


                                // Resize the DataGridView columns to fit the newly loaded content.
                                dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
                                // you can make it grid readonly.
                                dataGridView1.ReadOnly = true;

                                // finally bind the data to the grid
                                dataGridView1.DataSource = dbBindSource;

                                dataAdapter.Dispose();

                                //return true;
                            }

                            catch (Exception ex)
                            {
                                lblResult.Text = String.Format(
                                "ERROR: Failed to execute SQL command: {0}", ex.Message);
                                //return false;
                            }
                        }

                    }
                    //string SQLQuery = String.Format(
                    //    "SELECT Orders.OrderId, Orders.Amount, Orders.CreditCard " +
                    //    "FROM Users, Orders WHERE Users.Username='{0}' " +
                    //    "AND Users.UserId=Orders.UserId", _username);


            else if(method =="parameterized")
                    {
                        if (!_loggedIn)
                        {
                            lblResult.Text = "No user logged in";
                            //return false;
                        }
                        else
                        {

                            try
                            {
                                string SQLQueryParameterized = String.Format(
                                    "SELECT Distinct Orders.OrderId, Orders.Amount, Orders.CreditCard " +
                                    "FROM Users, Orders WHERE Users.Username='{0}' " +
                                    "AND Users.UserId=Orders.UserId", _username);

                                OracleCommand command = new OracleCommand(SQLQueryParameterized, _conn);

                                OracleDataAdapter dataAdapter = new OracleDataAdapter(command);
                                OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter);

                                // Populate a new data table and bind it to the BindingSource.
                                DataTable table = new DataTable();
                                table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                                dataAdapter.Fill(table);
                                BindingSource dbBindSource = new BindingSource();

                                dbBindSource.DataSource = table;


                                // Resize the DataGridView columns to fit the newly loaded content.
                                dataGridView2.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
                                // you can make it grid readonly.
                                dataGridView2.ReadOnly = true;

                                // finally bind the data to the grid
                                dataGridView2.DataSource = dbBindSource;

                                dataAdapter.Dispose();

                                //return true;
                            }

                            catch (Exception ex)
                            {

                                lblResult.Text = String.Format(
                                "ERROR: Failed to execute SQL command: {0}", ex.Message);
                                //return false;

                            }
                        }
                    }

            }

        

        protected void DisableLoggedInVisuals()
        {
            //if (page == "unparameterized")
            //{
                ButtonLogin.Enabled = true;
                ButtonLogin.Visible = true;
                ButtonLogout.Enabled = false;
                ButtonLogout.Visible = false;
                btnLoginParameterized.Enabled = true;
                btnLoginParameterized.Visible = true;
                ButtonLogoutParameterized.Enabled = false;
                ButtonLogoutParameterized.Visible = false;
                dataGridView1.Visible = false;
                dataGridView1.Enabled = false;
                dataGridView2.Visible = false;
                dataGridView2.Enabled = false;
            //}

            //else if (page == "unparameterized")
            //{
            //    ButtonLogin.Enabled = true;
            //    ButtonLogin.Visible = true;
            //    ButtonLogout.Enabled = false;
            //    ButtonLogout.Visible = false;
            //    ButtonLogoutParameterized.Enabled = false;
            //    ButtonLogoutParameterized.Visible = false;
            //    dataGridView1.Visible = false;
            //    dataGridView1.Enabled = false;
            //    dataGridView2.Visible = false;
            //    dataGridView2.Enabled = false;
            //    ButtonLogout.Enabled = false;
            //    ButtonLogout.Visible = false;
            //    ButtonLogoutParameterized.Enabled = false;
            //    ButtonLogoutParameterized.Visible = false;
            //}
        }

        protected void EnableLoggedInVisuals()
        {
            ButtonLogin.Enabled = false;
            ButtonLogin.Visible = false;
            ButtonLogout.Enabled = true;
            ButtonLogout.Visible = true;
            btnLoginParameterized.Enabled = false;
            btnLoginParameterized.Visible = false;
            ButtonLogoutParameterized.Enabled = true;
            ButtonLogoutParameterized.Visible = true;
            dataGridView1.Enabled = true;
            dataGridView1.Visible = true;
            dataGridView2.Enabled = true;
            dataGridView2.Visible = true;
        }
        
        #endregion

    }
}
